package com.demo2do.fms.admin.entity.report.export;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 * 
 * @author sanz
 *
 */
public class DriverDistanceReportSearch implements QueryBuilder {

    /** 运输公司 */
    private Long organizationId;
    
    /** 导出年月 */
    private String yearMonth;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;
    
    private QueryFilter queryFilter;
    
    private QueryOrder queryOrder;
    
    /**
     * The default constructor
     */
    public DriverDistanceReportSearch() {
        queryOrder = new QueryOrder("t1.employee_code", "ASC");
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("b", "trans_corp_id", organizationId);
            
            queryFilter.addSentence("TO_CHAR(b.leaving_time, 'YYYYMM') = " + yearMonth, Collections.emptyMap());
            
        }
        
        return queryFilter;
        
    }
    
    /**
     * @return
     */
    public String buildCountTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append(" SELECT COUNT(*) FROM (  SELECT T1.EMPLOYEE_CODE AS MAIN_CODE, T1.NAME AS MAIN_NAME, T2.EMPLOYEE_CODE AS FIRST_VICE_CODE, T2.NAME AS FIRST_VICE_NAME, SUM (T3.FULL_LOAD_DISTANCE) AS FULL_LOAD_SALARY, SUM (T3.EMPTY_LOAD_DISTANCE) AS EMPTY_LOAD_SALARY ")
                                       .append("FROM TBL_ALL_EMPLOYEE_INFO T1, TBL_ALL_EMPLOYEE_INFO T2, ")
                                            .append("(SELECT DISTINCT B.DISPATCH_ORDER_ID, C.MAIN_DRIVER_ID, C.FIRST_VICE_DRIVER_ID, A.FULL_LOAD_DISTANCE, A.EMPTY_LOAD_DISTANCE ")
                                            .append("FROM TBL_TRUCK_TRANSPORT_COST A, TBL_ALL_SHIPMENT B, TBL_TRUCK_DISPATCH_ORDER C ")
                                            .append("WHERE A.DISPATCH_ORDER_ID = B.DISPATCH_ORDER_ID AND B.IS_DISABLED = 0 AND C.ID = B.DISPATCH_ORDER_ID ")
                                            .append(getPreparedFilter(false).clauseSentence() + ") T3 ")
                                       .append("WHERE T3.MAIN_DRIVER_ID = T1.ID AND T3.FIRST_VICE_DRIVER_ID = T2.ID ")
                                       .append("GROUP BY T1.EMPLOYEE_CODE, T1.NAME, T2.EMPLOYEE_CODE, T2.NAME ) T_ALL");
        
        return buffer.toString();
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT MAIN_CODE AS mainCode, ")
                     .append("MAIN_NAME AS mainName, ")
                     .append("FIRST_VICE_CODE AS firstViceCode, ")
                     .append("FIRST_VICE_NAME AS firstViceName, ")
                     .append("FULL_LOAD_SALARY AS fullLoadSalary, ")
                     .append("EMPTY_LOAD_SALARY AS emptyLoadSalary ")
              .append("FROM (  SELECT T1.EMPLOYEE_CODE AS MAIN_CODE, T1.NAME AS MAIN_NAME, T2.EMPLOYEE_CODE AS FIRST_VICE_CODE, T2.NAME AS FIRST_VICE_NAME, SUM (T3.FULL_LOAD_DISTANCE) AS FULL_LOAD_SALARY, SUM (T3.EMPTY_LOAD_DISTANCE) AS EMPTY_LOAD_SALARY ")
                      .append("FROM TBL_ALL_EMPLOYEE_INFO T1, TBL_ALL_EMPLOYEE_INFO T2, ")
                      .append("(SELECT DISTINCT B.DISPATCH_ORDER_ID, C.MAIN_DRIVER_ID, C.FIRST_VICE_DRIVER_ID, A.FULL_LOAD_DISTANCE, A.EMPTY_LOAD_DISTANCE ")
                          .append("FROM TBL_TRUCK_TRANSPORT_COST A, TBL_ALL_SHIPMENT B, TBL_TRUCK_DISPATCH_ORDER C ")
                          .append("WHERE A.DISPATCH_ORDER_ID = B.DISPATCH_ORDER_ID AND B.IS_DISABLED = 0 AND C.ID = B.DISPATCH_ORDER_ID ")
                          .append(getPreparedFilter(false).clauseSentence() + ") T3 ")
                      .append("WHERE T3.MAIN_DRIVER_ID = T1.ID AND T3.FIRST_VICE_DRIVER_ID = T2.ID ")
               .append("GROUP BY T1.EMPLOYEE_CODE, T1.NAME, T2.EMPLOYEE_CODE, T2.NAME ")
               .append(queryOrder.sentence() + ") T_ALL");
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
      
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("mainCode", rs.getObject("mainCode"));
                result.put("mainName", rs.getObject("mainName"));
                result.put("firstViceCode", rs.getObject("firstViceCode"));
                result.put("firstViceName", rs.getObject("firstViceName"));
                result.put("fullLoadSalary", rs.getObject("fullLoadSalary"));
                result.put("emptyLoadSalary", rs.getObject("emptyLoadSalary"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return
     */
    public String getYearMonth() {
        return yearMonth;
    }

    /**
     * @return the beginIndex
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return the endIndex
     */
    public Integer getEndIndex() {
        return endIndex;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param yearMonth
     */
    public void setYearMonth(String yearMonth) {
        this.yearMonth = yearMonth;
    }

    /**
     * @param beginIndex the beginIndex to set
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }

    /**
     * @param endIndex the endIndex to set
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }

}
